﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

CREATE TABLE [dbo].[bht_Columns](
	[ColumnId] [int] NOT NULL,
	[DisplayName] [nvarchar](400) NOT NULL,
	[FormattingValue] [nvarchar](400) NULL,
 CONSTRAINT [PK_bht_NemoColumns] PRIMARY KEY CLUSTERED 
(
	[ColumnId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bht_Columns]  WITH CHECK ADD  CONSTRAINT [FK_bht_NemoColumns_bht_NemoColumns] FOREIGN KEY([ColumnId])
REFERENCES [dbo].[bht_Columns] ([ColumnId])
GO

ALTER TABLE [dbo].[bht_Columns] CHECK CONSTRAINT [FK_bht_NemoColumns_bht_NemoColumns]
GO

CREATE TABLE dbo.bht_ColumnTypes
	(
	ColumnType nvarchar(10) NOT NULL,
	EnumType nvarchar(50) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.bht_ColumnTypes ADD CONSTRAINT
	PK_bht_ColumnTypes PRIMARY KEY CLUSTERED 
	(
	ColumnType
	) ON [PRIMARY]

GO
ALTER TABLE dbo.bht_ColumnTypes SET (LOCK_ESCALATION = TABLE)
GO

INSERT INTO bht_ColumnTypes VALUES ('F', 'ValueField')
GO
INSERT INTO bht_ColumnTypes VALUES ('F1', 'OneReference')
GO
INSERT INTO bht_ColumnTypes VALUES ('FN', 'ManyReference')
GO
INSERT INTO bht_ColumnTypes VALUES ('I', 'FileField')
GO
INSERT INTO bht_ColumnTypes VALUES ('D', 'DateField')
GO
INSERT INTO bht_ColumnTypes VALUES ('DT', 'DateTimeField')
GO


CREATE PROCEDURE [dbo].[bhp_NemoGroupSchemeGet]
	@GroupId				INT
AS
BEGIN
	
	DECLARE
		@Sql NVARCHAR(MAX)
		
	SET @Sql = 'SELECT TOP 1 * FROM T' + CAST(@GroupId as NVARCHAR(10))
	
	EXEC sp_executesql @Sql

END

GO

CREATE PROCEDURE [dbo].[bhp_NemoGroupColumnsGet]
	@GroupId				INT
AS
BEGIN
	
	
	SELECT 
		id as ColumnId,
		name as ColumnName,
		ct.EnumType as ColumnType,
		r.rel as RelatedGroupId,
		ISNULL(c.DisplayName, name) as DisplayName,
		ISNULL(c.FormattingValue, '{0}') as FormattingValue,
		r.sort as ColumnSort
	FROM
		sTree t
		INNER JOIN sRel r ON t.id = r.field AND t.parent = r.tab
		INNER JOIN bht_ColumnTypes ct ON t.typ = ct.ColumnType
		LEFT JOIN bht_Columns c ON t.id = c.ColumnId
	WHERE
		parent = @GroupId
		AND t.del = 0
		AND r.del = 0
		AND ((t.typ NOT IN ('F', 'I', 'D', 'DT') AND r.rel > 0) OR (t.typ IN ('F', 'I', 'D', 'DT')))

END

GO
